Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

CASE Statements

Table of Contents

  1. Categorize Data
  2. Mapping
  3. Quick Form of CASE Statement
  4. Handling Nulls
  5. Conditional Aggregation

1. Categorize Data

Task 1 – Categorize orders into High / Medium / Low and sum sales

Create a report showing total sales for each category:

  • High: Sales over 50
  • Medium: Sales between 20 and 50
  • Low: Sales 20 or less

Return:

  • Category (High / Medium / Low)
  • TotalSales (sum of Sales per category)

Sort the result from highest to lowest TotalSales.

💡 Suggested Answers 
SELECT
    Category,
    SUM(Sales) AS TotalSales
FROM (
    SELECT
        OrderID,
        Sales,
        CASE
            WHEN Sales > 50 THEN 'High'
            WHEN Sales > 20 THEN 'Medium'
            ELSE 'Low'
        END AS Category
    FROM Sales.Orders
) AS t
GROUP BY Category
ORDER BY TotalSales DESC;

2. Mapping

Task 2 – Map country names to country codes

Retrieve customer details and add a country abbreviation column based on Country:

  • 'Germany' → 'DE'
  • 'USA' → 'US'
  • Anything else → 'n/a'

Return:

  • CustomerID, FirstName, LastName, Country, CountryAbbr
💡 Suggested Answers 
SELECT
    CustomerID,
    FirstName,
    LastName,
    Country,
    CASE 
        WHEN Country = 'Germany' THEN 'DE'
        WHEN Country = 'USA'     THEN 'US'
        ELSE 'n/a'
    END AS CountryAbbr
FROM Sales.Customers;

3. Quick Form of CASE Statement

Task 3 – Use quick-form CASE to map country codes

Retrieve customer details again, but this time:

  • Keep the full-form CASE version as CountryAbbr
  • Add a quick-form CASE version as CountryAbbr2, using CASE Country WHEN ...

Both should map:

  • 'Germany' → 'DE'
  • 'USA' → 'US'
  • Others → 'n/a'

Return:

  • CustomerID, FirstName, LastName, Country, CountryAbbr, CountryAbbr2
💡 Suggested Answers 
SELECT
    CustomerID,
    FirstName,
    LastName,
    Country,
    CASE 
        WHEN Country = 'Germany' THEN 'DE'
        WHEN Country = 'USA'     THEN 'US'
        ELSE 'n/a'
    END AS CountryAbbr,
    CASE Country
        WHEN 'Germany' THEN 'DE'
        WHEN 'USA'     THEN 'US'
        ELSE 'n/a'
    END AS CountryAbbr2
FROM Sales.Customers;

4. Handling Nulls

Task 4 – Clean NULL scores and compare averages

From Sales.Customers, calculate the average score of customers, treating NULL as 0.

Return:

  • CustomerID
  • LastName
  • Score
  • ScoreClean = Score, but NULL replaced with 0 using CASE
  • AvgCustomerClean = window AVG() of ScoreClean
  • AvgCustomer = window AVG() of the original Score (ignoring NULLs)
💡 Suggested Answers 
SELECT
    CustomerID,
    LastName,
    Score,
    CASE
        WHEN Score IS NULL THEN 0
        ELSE Score
    END AS ScoreClean,
    AVG(
        CASE
            WHEN Score IS NULL THEN 0
            ELSE Score
        END
    ) OVER () AS AvgCustomerClean,
    AVG(Score) OVER () AS AvgCustomer
FROM Sales.Customers;

5. Conditional Aggregation

Task 5 – Count high-sales orders per customer

Using Sales.Orders, count how many orders each customer made with Sales greater than 30.

Return:

  • CustomerID
  • TotalOrdersHighSales = number of orders where Sales > 30
  • TotalOrders = total number of orders per customer

Use CASE inside SUM() for conditional counting.

💡 Suggested Answers 
SELECT
    CustomerID,
    SUM(
        CASE
            WHEN Sales > 30 THEN 1
            ELSE 0
        END
    ) AS TotalOrdersHighSales,
    COUNT(*) AS TotalOrders
FROM Sales.Orders
GROUP BY CustomerID;